package select_

import (
	"database/sql"
	"fmt"
	"gitee.com/zy_blue7/go-gorm-learn/insert"
	"gorm.io/gorm"
	"time"
)

func Select(db *gorm.DB) {
	// GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误
	var user insert.User
	// 获取第一条记录（主键升序）
	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
	db.Debug().First(&user)
	/// First find first record that match given conditions, order by primary key
	//func (db *DB) First(dest interface{}, conds ...interface{}) (tx *DB)
	fmt.Println(user)

	// 获取一条记录，没有指定排序字段
	db.Take(&user)
	// SELECT * FROM users LIMIT 1;

	// 获取最后一条记录（主键降序）
	db.Last(&user)
	// SELECT * FROM users ORDER BY id DESC LIMIT 1;

	//result := db.First(&user)
	//result.RowsAffected // 返回找到的记录数
	//result.Error        // returns error or nil

	// 检查 ErrRecordNotFound 错误
	//errors.Is(result.Error, gorm.ErrRecordNotFound)

	// First 和 Last 会根据主键排序，分别查询第一条和最后一条记录。
	// 只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。
	// 此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。 例如:

	db.First(&user, 10)
	// SELECT * FROM users WHERE id = 10;

	db.First(&user, "10")
	// SELECT * FROM users WHERE id = 10;

	var users []insert.User
	db.Find(&users, []int{1, 2, 3})
	// SELECT * FROM users WHERE id IN (1,2,3);

	// todo SELECT * FROM `users` WHERE age=18 AND `users`.`deleted_at` IS NULL AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
	db.Debug().First(&user, "age=?", 18)
}

// SelectAll 检索全部对象
func SelectAll(db *gorm.DB) {
	var users []insert.User
	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL
	db.Debug().Find(&users)
	for _, user := range users {
		fmt.Println(user)
	}

	// todo string 条件
	/**

	// Get first matched record
	db.Where("name = ?", "jinzhu").First(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

	// Get all matched records
	db.Where("name <> ?", "jinzhu").Find(&users)
	// SELECT * FROM users WHERE name <> 'jinzhu';

	// IN
	db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
	// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

	// LIKE
	db.Where("name LIKE ?", "%jin%").Find(&users)
	// SELECT * FROM users WHERE name LIKE '%jin%';

	// AND
	db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
	// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

	// Time
	db.Where("updated_at > ?", lastWeek).Find(&users)
	// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

	// BETWEEN
	db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
	// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

	*/

	// SELECT * FROM `users` WHERE age in (18,19,20) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("age in ?", []int{18, 19, 20}).Find(&users)

	// todo struct /  map  条件
	/**

	// Struct
	db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

	// Map
	db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

	// Slice of primary keys
	db.Where([]int64{20, 21, 22}).Find(&users)
	// SELECT * FROM users WHERE id IN (20, 21, 22);

	*/

	// SELECT * FROM `users` WHERE `users`.`age` = 18 AND `users`.`deleted_at` IS NULL
	db.Debug().Where(&insert.User{Age: 18}).Find(&users)

	// SELECT * FROM `users` WHERE `age` = 18 AND `users`.`deleted_at` IS NULL
	db.Debug().Where(map[string]interface{}{"age": 18}).Find(&users)

	// todo  当使用 struct时，如果存在零值， 及 int =0， string==""  则不会当作查询条件，而map则相反
	// SELECT * FROM `users` WHERE `users`.`name` = 'zhangsan' AND `users`.`deleted_at` IS NULL
	db.Debug().Where(&insert.User{Name: "zhangsan", Age: 0}).Find(&users)
	// SELECT * FROM `users` WHERE `age` = 0 AND `name` = 'zhangs' AND `users`.`deleted_at` IS NULL
	db.Debug().Where(map[string]interface{}{"name": "zhangs", "age": 0}).Find(&users)

	// todo 指定结构体中的查询字段
	// SELECT * FROM `users` WHERE `users`.`name` = 'jinzhu' AND `users`.`age` = 0 AND `users`.`deleted_at` IS NULL
	db.Debug().Where(&insert.User{Name: "jinzhu"}, "name", "Age").Find(&users)

	// [rows:8] SELECT * FROM `users` WHERE `users`.`age` = 0 AND `users`.`deleted_at` IS NULL
	db.Debug().Where(&insert.User{Name: "jinzhu"}, "Age").Find(&users)

	user := insert.User{}

	// todo NOT 条件
	// [rows:1] SELECT * FROM `users` WHERE NOT name = 'zhangs' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
	db.Debug().Not("name = ?", "zhangs").First(&user)
	// Not In
	// [rows:30] SELECT * FROM `users` WHERE `name` NOT IN ('jinzhu','jinzhu 2') AND `users`.`deleted_at` IS NULL
	db.Debug().Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)

	// Struct
	// SELECT * FROM `users` WHERE (`users`.`name` <> 'jinzhu' AND `users`.`age` <> 18) AND `users`.`deleted_at` IS NULL AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
	db.Debug().Not(insert.User{Name: "jinzhu", Age: 18}).First(&user)

	// Not In slice of primary keys
	// SELECT * FROM `users` WHERE `users`.`id` NOT IN (1,2,3) AND `users`.`deleted_at` IS NULL AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
	db.Debug().Not([]int{1, 2, 3}).First(&user)

	// todo or	 条件
	// SELECT * FROM `users` WHERE ((name='zhang' and age = 18) OR id in (10,11,12)) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("name=? and age = ?", "zhang", 18).Or("id in ?", []int{10, 11, 12}).Find(&users)

	// Struct
	// SELECT * FROM `users` WHERE (name = 'jinzhu' OR (`users`.`name` = 'jinzhu 2' AND `users`.`age` = 18)) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("name = 'jinzhu'").Or(insert.User{Name: "jinzhu 2", Age: 18}).Find(&users)

	// Map
	// SELECT * FROM `users` WHERE (name = 'jinzhu' OR (`age` = 18 AND `name` = 'jinzhu 2')) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)

	// todo 查询特定字段
	// SELECT `name`,`age` FROM `users` WHERE `users`.`deleted_at` IS NULL
	db.Debug().Select("name", "age").Find(&users)
	// SELECT `name`,`age` FROM `users` WHERE `users`.`deleted_at` IS NULL
	db.Debug().Select([]string{"name", "age"}).Find(&users)
	// SELECT COALESCE(age,42) FROM `users`
	db.Debug().Table("users").Select("COALESCE(age,?)", 42).Rows()

	// todo Order 排序
	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY age desc, name
	db.Debug().Order("age desc, name").Find(&users)

	// Multiple orders
	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY age desc,name
	db.Debug().Order("age desc").Order("name").Find(&users)

	// todo Limit & Offset
	var users1 []insert.User
	var users2 []insert.User
	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 3
	db.Debug().Limit(3).Find(&users)

	// Cancel limit condition with -1
	//  SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 10 (users1)
	//  SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL (users2)
	db.Debug().Limit(10).Find(&users1).Limit(-1).Find(&users2)

	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL OFFSET 3
	db.Debug().Offset(3).Find(&users)

	// SELECT * FROM `users` WHERE `users`.`deleted_at` IS NULL LIMIT 10 OFFSET 5
	db.Debug().Limit(10).Offset(5).Find(&users)

	// Cancel offset condition with -1
	db.Debug().Offset(10).Find(&users1).Offset(-1).Find(&users2)
	// SELECT * FROM users OFFSET 10; (users1)
	// SELECT * FROM users; (users2)

	// todo Group By & Having
	type Result struct {
		Date  time.Time
		Total int
	}

	result := Result{}
	// SELECT name, sum(age) as total FROM `users` WHERE name LIKE 'group%' AND `users`.`deleted_at` IS NULL GROUP BY `name` ORDER BY `users`.`id` LIMIT 1
	db.Debug().Model(&insert.User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)

	// SELECT name, sum(age) as total FROM `users` WHERE `users`.`deleted_at` IS NULL GROUP BY `name` HAVING name = 'group'
	db.Debug().Model(&insert.User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)

	// todo Distinct
	//var results []Result
	// [rows:5] SELECT DISTINCT `name`,`age` FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY name, age desc
	db.Debug().Distinct("name", "age").Order("name, age desc").Find(&users)
	//  [rows:5] SELECT DISTINCT `name`,`age` FROM `users` WHERE `users`.`deleted_at` IS NULL ORDER BY name, age desc
	db.Debug().Select("id", "name", "age").Distinct("name", "age").Order("name, age desc").Find(&users)

	// todo  join

	// todo  scan
	// 类似于 find函数，将 查询出来的结果映射到 结构体中
	//  SELECT name,age FROM `users` WHERE name = 'Antonio'
	db.Debug().Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&users)

	// Raw SQL
	// SELECT name, age FROM users WHERE name = 'Antonio'
	db.Debug().Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&users)
}

// HighSelectAll 高级查询
func HighSelectAll(db *gorm.DB) {

	// todo 智能选择片段
	//GORM 允许通过 Select 方法选择特定的字段，如果您在应用程序中经常使用此功能，你也可以定义一个较小的结构体，以实现调用 API 时自动选择特定的字段，例如：
	type APIUser struct {
		ID   uint
		Name string
	}
	var apiUsers []APIUser
	// 查询时会自动选择 `id`, `name` 字段
	// [rows:30] SELECT `users`.`id`,`users`.`name` FROM `users` WHERE `users`.`deleted_at` IS NULL
	db.Debug().Model(&insert.User{}).Find(&apiUsers)
	for _, apiUser := range apiUsers {
		fmt.Println(apiUser)
	}

	// 注意 QueryFields 模式会根据当前 model 的所有字段名称进行 select。

	var user insert.User
	// Session Mode
	db.Session(&gorm.Session{QueryFields: true}).Find(&user)
	// SELECT `users`.`name`, `users`.`age`, ... FROM `users

	var users []insert.User
	// todo 子查询
	// 子查询可以嵌套在查询中，GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询
	// [rows:22] SELECT * FROM `users` WHERE age > (SELECT AVG(age) FROM `users`) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("age > (?)", db.Table("users").Select("AVG(age)")).Find(&users)

	subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
	// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE 'name%')
	db.Debug().Select("AVG(age) as avgage").Table("users").Group("name").Having("AVG(age) > (?)", subQuery).Rows()

	// todo from 子查询
	// GORM 允许您在 Table 方法中通过 FROM 子句使用子查询，例如
	// [rows:1] SELECT * FROM (SELECT `name`,`age`,`deleted_at` FROM `users` WHERE `users`.`deleted_at` IS NULL) as u WHERE age = 18 AND `u`.`deleted_at` IS NULL
	db.Debug().Table("(?) as u", db.Model(&insert.User{}).Select("name", "age", "deleted_at")).Where("age = ?", 18).Find(&insert.User{})

	subQuery1 := db.Model(&insert.User{}).Select("name", "deleted_at")
	subQuery2 := db.Model(&insert.User{}).Select("name", "deleted_at")
	//  [rows:1] SELECT * FROM (SELECT `name`,`deleted_at` FROM `users` WHERE `users`.`deleted_at` IS NULL) as u, (SELECT `name`,`deleted_at` FROM `users` WHERE `users`.`deleted_at` IS NULL) as p WHERE `u`.`deleted_at` IS NULL
	db.Debug().Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&insert.User{})

	// todo Group 条件
	// 使用 Group 条件可以更轻松的编写复杂 SQL
	// [rows:0] SELECT * FROM `users` WHERE ((name = 'pepperoni' AND (age = 16 OR age = 18)) OR (name = 'hawaiian' AND age = 11)) AND `users`.`deleted_at` IS NULL
	db.Debug().Where(
		db.Where("name = ?", "pepperoni").Where(db.Where("age = ?", 16).Or("age = ?", 18)),
	).Or(
		db.Where("name = ?", "hawaiian").Where("age = ?", 11),
	).Find(&insert.User{})

	// todo 带多个列带in
	// rows:0] SELECT * FROM `users` WHERE (name, age) IN (('jinzhu',18),('jinzhu2',19)) AND `users`.`deleted_at` IS NULL
	db.Debug().Where("(name, age) IN ?", [][]interface{}{{"jinzhu", 18}, {"jinzhu2", 19}}).Find(&users)

	// todo 命名参数
	// GORM 支持 sql.NamedArg 和 map[string]interface{}{} 形式的命名参数，
	// [rows:0] SELECT * FROM `users` WHERE name = 'jinzhu' OR name = 'jinzhu' or age=16 AND `users`.`deleted_at` IS NULL AND `users`.`id` = 5
	db.Debug().Where("name = @name1 OR name = @name1 or age=@age1", sql.Named("name1", "jinzhu"), sql.Named("age1", 16)).Find(&user)

	// [rows:0] SELECT * FROM `users` WHERE name = 'jinzhu' OR name = 'jinzhu' AND `users`.`deleted_at` IS NULL AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
	db.Debug().Where("name = @name1 OR name = @name1", map[string]interface{}{"name1": "jinzhu"}).First(&user)

	// todo Find 至 map  将查询结果放在map中
	result := map[string]interface{}{}
	db.Debug().Model(&insert.User{}).First(&result, "id = ?", 11)
	fmt.Println(result["name"])

	var results []map[string]interface{}
	db.Debug().Table("users").Find(&results)
	fmt.Println(results[0]["name"])

	// todo FirstOrInit  如果没有查询出记录，就根据 where 条件 初始化一个记录
	// 获取第一条匹配的记录，或者根据给定的条件初始化一个实例（仅支持 sturct 和 map 条件）

	user.ID = 0
	// 未找到 user，则根据给定的条件初始化一条记录
	// [rows:0] SELECT * FROM `users` WHERE `users`.`name` = 'non_existing' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
	db.Debug().FirstOrInit(&user, insert.User{Name: "non_existing"})
	fmt.Println(user.Name) // non_existing
	// user -> User{Name: "non_existing"}

	// 找到了 `name` = `jinzhu` 的 user
	db.Debug().Where(insert.User{Name: "jinzhu"}).FirstOrInit(&insert.User{})
	// user -> User{ID: 111, Name: "Jinzhu", Age: 18}

	user.ID = 10
	// 找到了 `name` = `jinzhu` 的 user
	db.Debug().FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
	fmt.Println(user.Name, user.Age) // jinzhu 18
	// user -> User{ID: 111, Name: "Jinzhu", Age: 18}

	/**
		todo  初始化带属性还可以是 Attr方法中的
		todo 如果没有找到记录，可以使用包含更多的属性的结构体初始化 user，Attrs 不会被用于生成查询 SQL

	// 未找到 user，则根据给定的条件以及 Attrs 初始化 user
	db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
	// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
	// user -> User{Name: "non_existing", Age: 20}

	// 未找到 user，则根据给定的条件以及 Attrs 初始化 user
	db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
	// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
	// user -> User{Name: "non_existing", Age: 20}

	// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs
	db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
	// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
	// user -> User{ID: 111, Name: "Jinzhu", Age: 18}
	*/

	// todo FirstOrCreate  如果没有查询出记录， 就根据 条件 创建一条记录，insert
	/**

	todo 如果没有找到记录，可以使用包含更多的属性的结构体创建记录，Attrs 不会被用于生成查询 SQL

	// 未找到 user，根据条件和 Assign 属性创建记录
	db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
	// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
	// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
	// user -> User{ID: 112, Name: "non_existing", Age: 20}

	// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs
	db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
	// user -> User{ID: 111, Name: "jinzhu", Age: 18}

	//todo 不管是否找到记录，Assign 都会将属性赋值给 struct，并将结果写回数据库

	// 未找到 user，根据条件和 Assign 属性创建记录
	db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
	// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
	// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
	// user -> User{ID: 112, Name: "non_existing", Age: 20}

	// 找到了 `name` = `jinzhu` 的 user，依然会根据 Assign 更新记录
	db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
	// UPDATE users SET age=20 WHERE id = 111;
	// user -> User{ID: 111, Name: "jinzhu", Age: 20}

	*/

	// todo 迭代
	// GORM 支持通过行进行迭代
	rows, _ := db.Model(&insert.User{}).Where("name = ?", "jinzhu").Rows()

	for rows.Next() {
		var user insert.User
		// ScanRows 方法用于将一行记录扫描至结构体
		db.ScanRows(rows, &user)

		// 业务逻辑...
	}

	// todo FindInBatches  用于批量查询并处理记录
	// 每次批量处理 100 条
	result1 := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
		for _, result11 := range results {
			// 批量处理找到的记录
			_ = result11
		}

		tx.Save(&results)

		//tx.RowsAffected // 本次批量操作影响的记录数

		//batch // Batch 1, 2, 3

		// 如果返回错误会终止后续批量操作
		return nil
	})
	_ = result1

}
